﻿using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
using QQZiFramework;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static TestProject1.PermissionSlutionDBData;

namespace TestProject1
{
    public class QueryBuilder
    {
        /// <summary>
        /// Permission项名称，空则使用表拼接
        /// </summary>
        /// <value></value>
        public string PermissionName { get; set; }
        public string ExtName { get; set; }

        /// <summary>
        /// 查询主表
        /// </summary>
        public string MainTable { get; set; }

        /// <summary>
        /// 主表的主键字段
        /// </summary>
        public string PrimaryKey { get; set; }

        /// <summary>
        /// 标记删除的字段
        /// </summary>
        public MarkDeleteFieldData MarkDeleteField { get; set; }

        /// <summary>
        /// 关联配置
        /// </summary>

        public List<TableJoinData> Joins { get; set; }

        /// <summary>
        /// 查询列配置
        /// </summary>
        public List<QueryFieldData> QueryFields { get; set; }

        // /// <summary>
        // /// 默认where
        // /// </summary>
        // /// <value></value>
        // public Dictionary<string,string> Defaults {get;set;}

        /// <summary>
        /// 可分组的列表，设置Permission的GroupByXXXX到whereField
        /// </summary>
        public List<GroupByData> GroupByList { get; set; }

        /// <summary>
        /// 数据权限表达式，过滤登录用户可看的数据范围
        /// </summary>
        /// <value></value>
        public string DataPermissionExpression { get; set; }

        /// <summary>
        /// 是否验证权限
        /// </summary>
        /// <value></value>
        public bool AuthCheck { get; set; }


        /// <summary>
        /// 新建时的默认值，指定列以及格式化
        /// 比如 DefaultValuesOnCreating.Add("create_time","{Now}");
        /// 格式化目前支持的有Now,UserId,UserName
        /// </summary>

        public Dictionary<string, string> DefaultValuesOnCreating { get; set; }

        /// <summary>
        /// 更新时的默认值，指定列以及格式化
        /// 比如 DefaultValuesOnCreating.Add("create_time","{Now}");
        /// 格式化目前支持的有Now,UserId,UserName
        /// </summary>

        public Dictionary<string, string> DefaultValuesOnUpdating { get; set; }


        public PermissionSlutionData ToSln(DBContext context, JToken permissionToken, JToken slnToken, string sln)
        {
            string slnName;
            PermissionSlutionData data = new PermissionSlutionData { EntityName = "EmptyEntity" };
            if (string.IsNullOrEmpty(PermissionName))
            {
                slnName = sln;
                PermissionName = MainTable;
                foreach (var joinItem in Joins) PermissionName += "-" + joinItem.Table;
            }
            else slnName = PermissionName;
            data.Permission = PermissionName;
            var qEntityConfig = QueryEntityConfig.Create(PermissionName);
            qEntityConfig.TableSql = MainTable + " a ";
            int asc2 = (int)'a';
            foreach (var joinItem in Joins)
            {
                asc2++;
                joinItem.Alias = ((char)asc2).ToString();
                string joinStr;
                if (joinItem.JoinType == TableJoinDataTpye.Inner) joinStr = " Join ";
                else joinStr = " left Join ";
                qEntityConfig.TableSql += joinStr + joinItem.Table + " " + joinItem.Alias + " on " + ConvertTalbeAlaisName(joinItem.Condition);
            }
            if (DataPermissionExpression != null && DataPermissionExpression.Trim().Length == 0) DataPermissionExpression = null;
            if (DataPermissionExpression != null)
            {
                //添加数据权限条件到默认查询
                if (qEntityConfig.Defaults == null) qEntityConfig.Defaults = new Dictionary<string, string>();
                //qEntityConfig.Defaults[Constants.DBCONTEXT_PERMISION_AUTH_DATA_FIELD] = "1";
                //qEntityConfig.Fields.Add(Constants.DBCONTEXT_PERMISION_AUTH_DATA_FIELD, new string[] { "null", ConvertTalbeAlaisName(DataPermissionExpression) });
            }

            //如果DefaultValuesOnCreating与DefaultValuesOnUpdating为空，按表里是否有关键字段自动添加
            if (DefaultValuesOnCreating == null)
            {
                DefaultValuesOnCreating = new Dictionary<string, string>();
                if (context.GetDBAccess().IsColumnExists(MainTable, "create_time"))
                {
                    DefaultValuesOnCreating.Add("create_time", "{Now}");
                }
                else if (context.GetDBAccess().IsColumnExists(MainTable, "createtime"))
                {
                    DefaultValuesOnCreating.Add("createtime", "{Now}");
                }
                if (context.GetDBAccess().IsColumnExists(MainTable, "creator"))
                {
                    DefaultValuesOnCreating.Add("creator", "{UserName}");
                }
                if (context.GetDBAccess().IsColumnExists(MainTable, "creator_id"))
                {
                    DefaultValuesOnCreating.Add("creator_id", "{UserId}");
                }
                if (context.GetDBAccess().IsColumnExists(MainTable, "tenant_id"))
                {
                    DefaultValuesOnCreating.Add("tenant_id", "{TenantId}");
                }
            }
            if (DefaultValuesOnUpdating == null)
            {
                DefaultValuesOnUpdating = new Dictionary<string, string>();
                if (context.GetDBAccess().IsColumnExists(MainTable, "update_time"))
                {
                    DefaultValuesOnUpdating.Add("update_time", "{Now}");
                }
                else if (context.GetDBAccess().IsColumnExists(MainTable, "updatetime"))
                {
                    DefaultValuesOnUpdating.Add("updatetime", "{Now}");
                }
                if (context.GetDBAccess().IsColumnExists(MainTable, "updater"))
                {
                    DefaultValuesOnUpdating.Add("updater", "{UserName}");
                }
                if (context.GetDBAccess().IsColumnExists(MainTable, "updater_id"))
                {
                    DefaultValuesOnUpdating.Add("updater_id", "{UserId}");
                }
            }
            //判断是否有主键
            if (PrimaryKey == null)
            {
                var pkScalar = context.ExecScalar(string.Format("select top 1 COL_NAME(object_id('{0}'),c.colid)	from sysobjects a,sysindexes b,sysindexkeys c	where a.name=b.name and b.id=c.id and b.indid=c.indid and a.xtype='PK' and a.parent_obj=object_id('{0}') 	and c.id=object_id('{0}')", MainTable));
                if (pkScalar != null && pkScalar != DBNull.Value)
                {
                    PrimaryKey = pkScalar.ToString();
                }
                else throw new Exception(MainTable + "表里没有定义主键，请手动提供");
            }
            string slnQueryFields = "";
            var dbFuncMap = context.GetDBAccess().FunctionFormatMap;
            string dateFormat = dbFuncMap["date"];
            string timeFormat = dbFuncMap["datetime"];
            foreach (var queryField in QueryFields)
            {
                //移除.前后的空格
                //var tempArr = RemoveBlankAbountDot(queryField).Split(' '); //是否有别名，有表达式的，比较日期，必需要有别名
                bool hasFunc = queryField.Name.IndexOf("(") > -1 || queryField.Name.IndexOf(")") > -1 || queryField.Name.Trim().IndexOf(" ") > 0;
                if (string.IsNullOrEmpty(queryField.Alias) && hasFunc) throw new Exception(queryField + "有表达式，必需要有别名");
                var tempArr2 = queryField.Name.Split('.');//取.之后的名字
                var fieldName = !string.IsNullOrEmpty(queryField.Alias) ? queryField.Alias : tempArr2[tempArr2.Length - 1];//有另外的使用别名，没有的，使用.之后的
                if (!hasFunc)
                {
                    if (queryField.DataType == QueryFieldDataType.Date)
                    {
                        queryField.Name = string.Format(dateFormat, queryField.Name);
                    }
                    else if (queryField.DataType == QueryFieldDataType.DateTime)
                    {
                        queryField.Name = string.Format(timeFormat, queryField.Name);
                    }
                    if (queryField.RealName == null) queryField.RealName = queryField.Name;
                }
                else if (queryField.RealName == null)
                {
                    // throw new Exception("自定义表达式的情况下，必需提供表里的字段名");
                }
                if (queryField.RealName != null)
                {
                    //别名
                    if (queryField.RealName.IndexOf('.') == -1) queryField.RealName = "a." + queryField.RealName;
                    else queryField.RealName = ConvertTalbeAlaisName(queryField.RealName);
                }
                var fieldSelectExp = queryField.Name;//查询表达式
                if (tempArr2.Length == 1 && fieldName == PrimaryKey) fieldSelectExp = "a." + fieldSelectExp;//主键没带表名的，强制带表名
                else
                {
                    fieldSelectExp = ConvertTalbeAlaisName(fieldSelectExp);//带表名的，使用别名重写
                }
                if (string.IsNullOrEmpty(queryField.CustomWhere))
                {
                    if (hasFunc && queryField.RealName != null)
                    {
                        qEntityConfig.Fields.Add(fieldName, new string[] { fieldSelectExp, queryField.RealName + "='{0}'" });
                    }
                    else
                    {
                        qEntityConfig.Fields.Add(fieldName, new string[] { fieldSelectExp });
                    }
                }
                else
                {
                    qEntityConfig.Fields.Add(fieldName, new string[] { fieldSelectExp, ConvertTalbeAlaisName(queryField.CustomWhere) });
                }
                if (!string.IsNullOrEmpty(queryField.DefaultWhereValue) && !string.IsNullOrEmpty(fieldName))
                {
                    if (qEntityConfig.Defaults == null) qEntityConfig.Defaults = new Dictionary<string, string>();
                    qEntityConfig.Defaults.Add(fieldName, queryField.DefaultWhereValue);
                }
                if (!string.IsNullOrEmpty(fieldSelectExp))
                {
                    if (queryField.RealName != null)
                    {
                        if (queryField.DataType != QueryFieldDataType.String)
                        {
                            //添加between的表达式
                            qEntityConfig.Fields.Add(fieldName + "_BETWEEN", new string[] { fieldSelectExp, queryField.RealName + " between '{0}' and '{1}'" });
                        }
                        else
                        {
                            //字符串类型的添加like
                            qEntityConfig.Fields.Add(fieldName + "_LIKE", new string[] { fieldSelectExp, queryField.RealName + " like '%{0}%'" });
                        }
                    }
                    if (queryField.DefaultQuery) slnQueryFields += "," + fieldName;
                }
            }
            //判断主表是否包含租户，没有的话，就算是saas也不要添加tenantid条件(onquery会默认加，所以permission配置中要做一个空的)
            var existsTenantField = context.GetDBAccess().IsColumnExists(MainTable, "tenant_id");
            //object tempScalar = context.ExecScalar(" SELECT TOP 1 1 FROM    INFORMATION_SCHEMA.COLUMNS WHERE   [TABLE_NAME] = '" + MainTable + "' AND [COLUMN_NAME] = 'TENANT_ID'  ");
            if (existsTenantField && !qEntityConfig.Fields.ContainsKey("tenant_id"))
            {
                qEntityConfig.Fields.Add("tenant_id", new string[] { "a.tenant_id" });
                //2022/8/25 不需要该流程
                //qEntityConfig.Fields.Add("TenantId", new string[] { "NULL", "1=1" });
            }


            //标记删除列定义
            if (MarkDeleteField == null && context.GetDBAccess().IsColumnExists(MainTable, "deleted"))
            {
                MarkDeleteField = new MarkDeleteFieldData { FieldName = "deleted", DeleteValue = "1" };
            }
            if (MarkDeleteField != null)
            {
                var dbFieldName = "a." + MarkDeleteField.FieldName;
                qEntityConfig.Fields.Add("MARK_DELETE", new string[] { dbFieldName, dbFieldName + " = " + MarkDeleteField.DeleteValue });
                qEntityConfig.Fields.Add("NOT_MARK_DELETE", new string[] { dbFieldName, dbFieldName + " is null or " + dbFieldName + " <> 1" });
            }

            //可提供的group by
            if (GroupByList != null && GroupByList.Count > 0)
            {
                // foreach (var item in GroupByList)
                // {
                // var regex = new Regex(@"/\((.+?)\)/g");//匹配括号，分组的查询字段一般会跟随聚合函数
                // var allFields = "";
                // var queryFieldList = new List<string>();
                // foreach (var itemQueryField in item.QueryFields.Split(','))
                // {
                //     var fieldArr = itemQueryField.Split("as");
                //     if (fieldArr.Length < 2) throw new ClientException("必需包括as进行重命名");
                //     var name = fieldArr[1].Trim();
                //     //判断是否与其它Fields字段同名
                //     if (qEntityConfig.Fields.ContainsKey(name)) throw new ClientException("分组查询的字段别名不能与“查询列”里面字段名的重复！");
                //     allFields += "," + name;
                //     string value = ConvertTalbeAlaisName(fieldArr[0].Trim());//字段值，并且替换表别名                        
                //     qEntityConfig.Fields.Add(name, new string[] { value });
                //     queryFieldList.Add(name);
                // }
                // item.QueryFieldArray = queryFieldList.ToArray();                    
                // qEntityConfig.Fields.Add(item.Name, new string[] { "NULL", "group by " + item.GroupByFields, "NULL" });
                // }
                for (var i = 0; i < GroupByList.Count; i++)
                {
                    var item = GroupByList[i];
                    var groupByWhereField = "";
                    foreach (var itemQueryField in item.QueryFields.Split(','))
                    {
                        if (!qEntityConfig.Fields.ContainsKey(itemQueryField)) throw new ClientException("“查询列”中没有定义" + itemQueryField);

                    }
                    foreach (var itemQueryField in item.GroupByFields.Split(','))
                    {
                        if (!qEntityConfig.Fields.ContainsKey(itemQueryField)) groupByWhereField += "," + itemQueryField;
                        else groupByWhereField += "," + qEntityConfig.Fields[itemQueryField][0];
                    }
                    groupByWhereField = groupByWhereField.Substring(1);
                    string permissionGroupName = item.Name;
                    if (!item.Name.StartsWith("GROUP_BY_", StringComparison.OrdinalIgnoreCase)) permissionGroupName = "GROUP_BY_" + item.Name;
                    qEntityConfig.Fields.Add(permissionGroupName, new string[] { "NULL", "group by " + groupByWhereField, "NULL" });
                }
            }

            //set permissoin linq
            var str = JsonConvert.SerializeObject(qEntityConfig);
            var jtoken = JToken.Parse(str);
            if (permissionToken[data.Permission] != null && permissionToken[data.Permission].HasValues)
            {
                permissionToken[data.Permission].Parent.Remove();
            }
                  ((JObject)permissionToken).Add(data.Permission, jtoken);
            //set sln linq
            if (slnQueryFields.Length == 0) throw new ClientException("至少要有一个默认查询的列！");
            data.QueryField = slnQueryFields.Substring(1);
            data.Builder = this;
            str = JsonConvert.SerializeObject(data);
            jtoken = JToken.Parse(str);
            if (slnToken[slnName] != null && slnToken[slnName].HasValues)
            {
                slnToken[slnName].Parent.Remove();
            }
            ((JObject)slnToken).Add(slnName, jtoken);
            return data;
        }

        private string ConvertTalbeAlaisName(string strName)
        {
            if (ContainsTable(strName, MainTable))
            {
                strName = strName.Replace(MainTable + ".", "a.");//改为主表别名.列名
            }

            foreach (var joinItem in Joins)
            {
                if (ContainsTable(strName, joinItem.Table))
                    strName = strName.Replace(joinItem.Table + ".", joinItem.Alias + ".");//改为表别名.列名
            }
            return strName;
        }

        private bool ContainsTable(string field, string table)
        {
            int pos = field.IndexOf(table);
            if (pos == -1) return false;
            if (pos == 0) return true;
            //判断前一个字符是否是字母或者数字或者下划线
            int asc = (int)field[pos - 1];
            if ((asc > 64 && asc < 91) || (asc > 96 && asc < 123) || asc == 95) return false;
            return true;
        }

        public string RemoveBlankAbountDot(string str)
        {
            str = str.Trim();
            if (str[0] == '.' || str[str.Length - 1] == '.') throw new Exception("列表达式错误，请全表名.列名");
            int findStart = 0;
            while (true)
            {
                int pos = str.IndexOf('.', findStart);
                if (pos == -1) break;
                int posBefore = -1, posAfter = -1;
                for (int i = pos + 1; i < str.Length; i++)
                {
                    if (str[i] == ' ') posAfter = i;
                    else break;
                }
                if (posAfter != -1)
                {
                    //.后面有空格
                    str = str.Substring(0, pos + 1) + str.Substring(posAfter + 1, str.Length - posAfter - 1);
                }
                for (int i = pos - 1; i > -1; i--)
                {
                    if (str[i] == ' ') posBefore = i;
                    else break;
                }
                if (posBefore != -1)
                {
                    //aa .前面有空格
                    str = str.Substring(0, posBefore) + str.Substring(pos, str.Length - pos);
                }
                findStart = pos + 1;
            }
            return str;
        }

        // private bool IsMySql(DBContext context)
        // {
        //     return context.GetConfig().DatabaseType == "mysql";
        // }

        public class TableJoinData
        {
            public string Table { get; set; }
            public string Alias { get; set; }
            public TableJoinDataTpye JoinType { get; set; }
            public string Condition { get; set; }
        }

        public enum TableJoinDataTpye { Inner, Left }

        public enum QueryFieldDataType { String = 1, Number = 2, Date = 3, DateTime = 4 }

        public class QueryFieldData
        {
            /// <summary>
            /// 查询的表达式（正常是列名）
            /// </summary>
            /// <value></value>
            public string Name { get; set; }

            /// <summary>
            /// 别名
            /// </summary>
            /// <value></value>
            public string Alias { get; set; }

            /// <summary>
            /// 真实的表里的列名
            /// </summary>
            /// <value></value>
            public string RealName { get; set; }

            /// <summary>
            /// 字段类型
            /// </summary>
            /// <value></value>
            public QueryFieldDataType DataType { get; set; }

            /// <summary>
            /// 自定义where条件
            /// </summary>
            /// <value></value>
            public string CustomWhere { get; set; }

            /// <summary>
            /// 默认条件值， 如果设置，则查询默认带条件
            /// </summary>
            /// <value></value>
            public string DefaultWhereValue { get; set; }

            /// <summary>
            /// 默认加到查询，不传queryField的时候
            /// </summary>
            /// <value></value>
            public bool DefaultQuery { get; set; }

            public QueryFieldData()
            {
                DefaultQuery = true;
            }
        }

        /// <summary>
        /// 分组查询配置
        /// </summary>
        public class GroupByData
        {
            /// <summary>
            /// 自定义分组的名字
            /// </summary>
            /// <value></value>
            public string Name { get; set; }

            /// <summary>
            /// 查询的列名（别名，对应Permission中的字段名，以逗号隔开）
            /// </summary>
            /// <value></value>
            public string QueryFields { get; set; }

            /// <summary>
            /// 分组的列名（按这些列groupby）
            /// </summary>
            /// <value></value>
            public string GroupByFields { get; set; }

            // /// <summary>
            // /// 解析后的GroupByFields列名数组
            // /// </summary>
            // /// <value></value>
            // public string[] QueryFieldArray { get; set; }

        }

        public class MarkDeleteFieldData
        {
            /// <summary>
            /// 字段名
            /// </summary>
            public string FieldName { get; set; }

            /// <summary>
            /// 代表删除的值
            /// </summary>
            public string DeleteValue { get; set; }
        }
    }
}
